﻿# encoding:utf-8
# 访问数据库 用户,这里把所有的用户、群、好友等角色都当成 User 保存
from wxpyImport import *
from db.wxpyDbBase import *
from db.wxpyDbDict2Sql import *

# wxpy.User.raw 中不用更新到数据库中的字段项
# WebWxPluginSwitch 是机器人自己的时候才会有的
def getUserRawExceptKeys():
    return ['self','MemberList','WebWxPluginSwitch','HeadImgFlag','puid','is_group']

# 为了能用 dbAddOrUpdateUserAll 方法插入自己构造的数据，而模拟 wxpy.Chat 构造出的一个类似的类
class dbUser(object):
    # raw 同 wxpy.User.raw
    # puid 同 wxpy.User.puid
    # isgroup True or False
    def __init__(self, raw, puid, isgroup):
        self.raw = raw
        self.puid = puid
        self.isgroup = isgroup
    
    # 经常用 isGroup (通过前几个字符)来判断是群还是用户
    def __str__(self):
        sign = '<Group:'
        if(self.isgroup):
            sign = '<User:'
        name = ''
        if self.raw is not None and hasattr(self.raw, 'nick_name'):
            name = self.raw.nick_name
        return sign+name+'>'

# 判断该值是否是 user_all 中的默认值
def isDbUserAllDefaultValue(value):
    if isinstance(value, str):
        return ''==value
    # else: 数值型
    return -1==value

# 判断该值是否是 user_big 中的默认值
def isDbUserBigDefaultValue(value):
    if isinstance(value, str):
        return ''==value
    # else: 数值型
    return 0==value or -1==value
    
# resetDb 如果有异常发生, true 表示重新连接数据库再执行一次, false 表示忽略
def dbGetUser(dbd, puid, resetDb=False):
    if puid is None:
        logError('dbGetUser puid empty')
        return

    sql = "select * from user where puid = %s order by update_time desc limit 1"

    results = None
    try:
        cursor = getCursorColName(dbd, resetDb)
        cursor.execute(sql,(puid))
        results = cursor.fetchone()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetUser err: '+str(e))
        if not resetDb: # 防止死循环
            return dbGetUser(dbd, puid, True)
    
    return results
        
def dbGetUserBig(dbd, puid, resetDb=False):
    sql = "select * from user_big where puid = %s order by update_time desc limit 1"

    results = None
    try:
        cursor = getCursor(dbd, resetDb)
        # dbd.db.ping(reconnect=True)
        cursor.execute(sql,(puid))
        results = cursor.fetchone()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetUserBig err: '+str(e))
        if not resetDb: # 防止死循环
            return dbGetUserBig(dbd, puid, True)
    finally:
        return results

# 保存用户信息，puid 存在则更新，不存在新增
def dbAddUser(dbd, user, resetDb=False):
    try:
        # 先插入到 user_all 表
        if not resetDb:
            dbAddOrUpdateUserAll(dbd, user)
    except Exception as e:
        dealException()
        logError('dbAddUse dbAddOrUpdateUserAl err: '+str(e))

    try:
        isgroup = 1 if isGroup(user) else 0
        userOld = dbGetUser(dbd, user.puid)
        if userOld is not None:
            if userOld['is_group']!=isgroup: # 假如用户与群 puid 相同，则保存两个用户
                logError('--------------------userOld!=isgroup')
                userOld = None
        userOldBig = dbGetUserBig(dbd, user.puid)
        sql = "update user set nick_name = %s, remark_name = %s, avatar = '' where is_group = %s and puid = %s order by update_time desc limit 1"
        sqlBig = "update user_big set raw = %s where puid = %s order by create_time desc limit 1"
        isAdd = False

        # 某些消息昵称是空？还是个人设置没有昵称?
        nick_name = user.nick_name
        if nick_name is None:
            logError('nick_name empty: '+str(user))
            nick_name = ''
        remark_name = ''
        if not isgroup:
            remark_name = user.remark_name
            if remark_name is None:
                remark_name = ''
        
        if userOld is None:
            sql = "INSERT INTO user (nick_name, remark_name, is_group, puid) VALUES(%s, %s, %s, %s)"
            isAdd = True
        else: # 如果最新的值为空而数据库中有值，则保留数据库中的值
            if '' == nick_name:
                nick_name = userOld['nick_name']
            if '' == remark_name:
                remark_name = userOld['remark_name']
                
        if userOldBig is None:
            sqlBig = "INSERT INTO user_big (raw, puid) VALUES(%s, %s)"

    
        cursor = getCursor(dbd, resetDb)
        # 存大块聊天记录
        cursor.execute(sql,(nick_name, remark_name, isgroup, user.puid))
        cursor.execute(sqlBig,(str(user.raw), user.puid))
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbAddUse err: '+str(e))
        logError('dbAddUse err nick_name len: '+str(len(nick_name)))
        logError('dbAddUse err remark_name len: '+str(len(remark_name)))
        # puid 已存在，把用户数据打印出来
        if 'Duplicate entry' in str(e):
            logError(str(user.raw))
        dbd.db.rollback()
    else:
        tmpStr = 'add user success: '
        if not isAdd:
            tmpStr = 'update user success: '

        logInfo(tmpStr + str(user.puid))


# 从 user_all 中取一条数据
# isgroup: True False
def dbGetUserAll(dbd, puid, isgroup):
    if puid is None:
        logError('dbGetUserAl puid empty')
        return None
    
    groupStr = '1' if isgroup else '0'

    sql = "select * from user_all where puid = %s and is_group="+groupStr

    results = None
    try:
        cursor = getCursor(dbd)
        cursor.execute(sql,(puid))
        results = cursor.fetchone()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetUserAl err: '+str(e))
        logError('dbGetUserAl puid: '+puid+' sql: '+sql)
    
    return results

# 有就更新不为空的字段，没有就插入表 user_all
# return 是否成功
def dbAddOrUpdateUserAll(dbd, user):
    puid = None
    try:
        puid = user.puid
    except Exception as e:
        dealException()
        logError('dbAddOrUpdateUserAl puid err: '+str(e))
        return False

    oldUser = dbGetUserAll(dbd, puid, isGroup(user))
    if oldUser is None:
        return dbAddUserAll(dbd, user)

    # 已存在的情况
    return dbUpdateUserAllDict(dbd, user)

# 根据 user 更新到 user_all 表中，忽略为 '' 的字段（不考虑对象不存在的情况）
# 返回是否成功
def dbUpdateUserAllDict(dbd, user):
    dict = None
    try:
        dict = dbUserReplaceEmojiDict(user.raw)
    except Exception as e:
        dealException()
        logError('dbUpdateUserAllDic user.raw err: '+str(e))
        return False
    
    try:
        groupStr = '1' if isGroup(user) else '0'

        # 这两个是群里面的对象，不用存
        keysExcept = getKeysWhileValueIsEmpty(dict, getUserRawExceptKeys())
        sql = dict2SqlUpdate(dict, 'user_all', keysExcept)
        sql += " where puid =\'"+user.puid +"\' and is_group ="+groupStr
        values = valuesExcept(dict, keysExcept)
        cursor = getCursorColName(dbd)
        num = cursor.execute(sql,values)
        if num<=0:
            logInfo('no update puid: '+user.puid+' is_group: '+groupStr)
            # logInfo(sql)
            # logInfo(values)
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbUpdateUserAllDic err2: '+str(e))
        return False

    return True

# 将 dict 中可能有 emoji 字符的过滤掉
# 返回过滤后的 dict
def dbUserReplaceEmojiDict(dict):
    tmp = {}
    for k in dict.keys():
        if k in ['NickName','DisplayName','RemarkName','Signature',
        'nick_name', 'display_name', 'remark_name', 'signature']:
            tmp[k] = replaceEmojiXml(dict[k])
        else:
            tmp[k] = dict[k]
    
    return tmp

# 增加到表 user_all 中，不考虑已存在情况
# return 是否成功
def dbAddUserAll(dbd, user):
    dict = None
    try:
        dict = dbUserReplaceEmojiDict(user.raw)
    except Exception as e:
        dealException()
        logError('dbAddUserAl user.raw err: '+str(e))
        return False

    try:
        dict['is_group'] = 1 if isGroup(user) else 0

        # 这两个是群里面的对象，不用存
        keysExcept = getKeysWhileValueIsEmpty(dict, getUserRawExceptKeys())
        keysExcept.remove('puid')
        keysExcept.remove('is_group')
        dict['puid'] = user.puid
        sql = dict2SqlInsert(dict, "user_all", keysExcept)
        values = valuesExcept(dict, keysExcept)
        cursor = getCursorColName(dbd)
        cursor.execute(sql,values)
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        errStr = str(e)
        if 'Data too long for column' in errStr:
            logError('dbAddUserAl err puid: '+user.puid)
        logError('dbAddUserAl err: '+errStr)
        logError('sql: '+sql)
        logError('values: '+str(values))
        return False

    logInfo('dbAddUserAl success: '+user.puid)
    return True

# 更新用户头像, 包含 user 及 user_all
# return 是否成功
def dbUpdateUserAvatar(dbd, puid, avatarUrl, isgroup):

    sql = "update user u set u.avatar = %s where u.puid = %s order by u.create_time desc limit 1"
    #eee 这里没有用 is_group 如果群与人重 puid 时会覆盖
    sqlAll = "update user_all u set u.avatar = %s where u.puid = %s and u.is_group=%s order by u.create_time desc limit 1"

    try:
        cursor = getCursor(dbd)
        num = cursor.execute(sql,(avatarUrl, puid))
        if num<=0:
            logError('dbUpdateUserAvata1 no update: '+puid)
        num = cursor.execute(sqlAll,(avatarUrl, puid, 1 if isgroup else 0))
        if num<=0:
            logError('dbUpdateUserAvata2 no update: '+puid)
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbUpdateUserAvata err: '+str(e))
        return False

    return True

# 以指定字段名从 user 表中分组获取所有用户的不同字段
# 比如找到表中所有不同的 puid
def dbGetAllUserDictsByFileName(dbd, fileName):
    sql = "select %s from user group by %s order by update_time desc"

    results = None
    try:
        cursor = getCursor(dbd)
        cursor.execute(sql,(fileName))
        results = cursor.fetchall()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetAllUserDictsByFileNam err: '+str(e))
    
    return results